package pc

import (
	. "chess_platform/internal/common"
	"fmt"
	"github.com/astaxie/beego/orm"
	"math"
	"time"
)

//从day_game里列出今天的开桌数,及小局数
func getOneDayChess(day int64, multiOrm ...orm.Ormer) orm.Params {
	tableName := GetTableByTimestamp(TableDayGame, day)
	//检查表是否存在
	if !CheckTableIsExist(tableName) {
		return orm.Params{
			"table_num": 0, //开桌数
			"round_num": 0, //每一小局
			"day":       day,
		}
	}

	//开桌数,及小局数
	sql := fmt.Sprintf(`
		SELECT
			COUNT(DISTINCT tid) AS table_num, COUNT(1) AS round_num
		FROM
			%v
	`, tableName)
	o := NewOrm(multiOrm, DBChessLog)
	type Chess struct {
		TableNum int64 `json:"table_num"`
		RoundNum int64 `json:"round_num"`
	}
	var chess Chess
	o.Raw(sql).QueryRow(&chess)
	return orm.Params{
		"table_num": chess.TableNum,
		"round_num": chess.RoundNum,
		"day":       day,
	}
}

//获取一天的金流
func getOneDayTrans(t int64, multiOrm ...orm.Ormer) (oneDT OneDayTrans, err error) {
	tableName := GetTableByTimestamp(TableDayTrans, t)
	if !CheckTableIsExist(tableName) {
		return oneDT, nil
	}
	o := NewOrm(multiOrm)
	o.Using(DBChessLog)
	//send为正数
	sqlSend := fmt.Sprintf(`
		SELECT
			IF(SUM(amount) is NULL,0,SUM(amount))
		FROM
			%v
		WHERE
			amount > 0
	`, tableName)
	err = o.Raw(sqlSend).QueryRow(&oneDT.Send)
	if CheckNoExist(err) {
		return oneDT, nil
	} else if err != nil {
		return oneDT, err
	}
	//cost掉的为负数
	sqlCost := fmt.Sprintf(`
		SELECT
			IF(SUM(amount) is NULL,0,SUM(amount))
		FROM
			%v
		WHERE
			amount < 0
	`, tableName)
	err = o.Raw(sqlCost).QueryRow(&oneDT.Cost)
	if CheckNoExist(err) {
		return oneDT, nil
	} else if err != nil {
		return oneDT, err
	}
	//remain为上面两者之和
	oneDT.Remain = oneDT.Send + oneDT.Cost
	return
}

func ListDayChess(p ListDayParams) []orm.Params {
	var chess []orm.Params
	sql := fmt.Sprintf(`
			SELECT
				*
			FROM
				%v
			WHERE
				day >= %v AND day <= %v
			ORDER BY
				day DESC
		`, TableDayTransStat, p.StartTime, p.EndTime)
	o := NewDBOrm(DBChessLog)
	var rest []orm.Params
	o.Raw(sql).Values(&rest)
	if IsToday(p.EndTime) { //如果是今天，把今天的数据放在最前面返回
		tmp := getOneDayChess(p.EndTime)
		data, _ := getOneDayTrans(p.EndTime)
		tmp["cost"] = data.Cost
		tmp["send"] = data.Send
		tmp["remain"] = data.Remain
		chess = append(chess, tmp)
		chess = append(chess, rest...)
	} else {
		chess = rest
	}
	return chess
}

//列出各款游戏统计数据(day_game_stat表),桌子数，小局数，钻石数
func ListDayChessDetail(day int64) ([]orm.Params, error) {
	_, end := StartEndTimeByTimestamp(day)
	where := fmt.Sprintf("create_time = %v", end-1)
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
	`, TableDayGameStat, where)
	o := NewDBOrm(DBChessLog)
	var userInfo []orm.Params
	_, err := o.Raw(sql).Values(&userInfo)
	if CheckError(err) {
		return userInfo, err
	}
	return userInfo, nil
}

//列出今天实时的各款游戏统计数据(day_game_stat表),桌子数，小局数，钻石数
func ListTodayChessDetail(day int64) ([]orm.Params, error) {
	type ChessStat struct {
		TableNum int64 `json:"table_num"` //桌子数
		RoundNum int64 `json:"round_num"` //小局数
		Diamond  int64 `json:"diamond"`   //消耗的钻石数
		GameType int64 `json:"game_type"`
		PlayType int64 `json:"play_type"`
	}
	var cs []ChessStat
	start, end := StartEndTimeByTimestamp(day)

	sql := fmt.Sprintf(`
		SELECT
			COUNT(1) AS table_num,IF (SUM(p.curround) is NULL,0,SUM(p.curround)) AS round_num,
			IF (SUM(p.fee) is NULL,0,SUM(p.fee)) AS diamond,
			p.gametype AS game_type,p.playtype AS play_type
		FROM
			%v AS p
		WHERE
			p.endtime >= %v AND p.endtime <= %v
		GROUP BY p.gametype,p.playtype
	`, TableRoom, start, end)
	sqlUser := fmt.Sprintf(`
		SELECT
			COUNT(DISTINCT r.uid) AS user_num
		FROM
			%v AS p
		LEFT JOIN
			%v AS r
		ON p.tableid = r.tableid
		WHERE
			p.endtime >= %v AND p.endtime <= %v AND p.gametype = ? AND p.playtype = ?
	`, TableRoom, TableRoomLog, start, end)
	o := NewDBOrm(DBChess)
	_, err := o.Raw(sql).QueryRows(&cs)
	if err != nil {
		LogPc.Error(fmt.Sprintf("统计每天每款游戏时出错:%v", err))
		return nil, err
	}
	var gameStat []orm.Params
	for _, v := range cs {
		var userNum int64
		o.Raw(sqlUser, v.GameType, v.PlayType).QueryRow(&userNum)
		gameStat = append(gameStat, map[string]interface{}{
			"create_time": time.Now().Unix(),
			"game_id":     GenGameId(v.GameType, v.PlayType),
			"table_num":   v.TableNum,
			"round_num":   v.RoundNum,
			"diamond":     v.Diamond,
			"user_num":    userNum,
		})
	}
	return gameStat, nil
}

//列出各款游戏统计数据(day_game_stat表),桌子数，小局数，钻石数
//以游戏做第一视角
func ListGameTrans(t ListDayParams, gameId, groupId int64) ([]orm.Params, error) {
	where := " 1=1 "
	if t.StartTime > 0 && t.EndTime > t.StartTime {
		where = fmt.Sprintf(" create_time > %v AND create_time <= %v", t.StartTime, t.EndTime)
	} else {
		//查询昨天的数据
		_, end := StartEndTimeByTimestamp(time.Now().Unix() - 86400)
		where = fmt.Sprintf("create_time = %v", end-1)
	}
	if gameId != 0 {
		where = where + fmt.Sprintf(" AND game_id = %v", gameId)
	}
	var userInfo []orm.Params
	if groupId != 0 {
		gameId := listGroup(groupId)
		if len(gameId) == 0 { //说明没有游戏可以查看
			return userInfo, nil
		}
		gameIdStr := Join(gameId, ",")
		where = where + fmt.Sprintf(" AND game_id IN (%v) ", gameIdStr)
	}
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		ORDER BY
			create_time
		DESC
	`, TableDayGameStat, where)
	o := NewDBOrm(DBChessLog)
	_, err := o.Raw(sql).Values(&userInfo)
	if CheckError(err) {
		return userInfo, err
	}
	return userInfo, nil
}

func listGroup(groupId int64) []int64 {
	sql := fmt.Sprintf(`
		SELECT
			game_id
		FROM
			%v
		WHERE
			group_id = %v
	`, TableGroupGame, groupId)
	o := NewDBOrm(DBChessCenter)
	var gameId []int64
	o.Raw(sql).QueryRows(&gameId)
	return gameId
}

type GroupGame struct {
	GameId int64  `json:"game_id"`
	Name   string `json:"name"`
}

//根据大区列出游戏
func ListGameByGroupId(p ListParams, groupId int64) ([]GroupGame, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	var gg []GroupGame
	where := " is_publish = 1 AND is_delete = 0 "
	if groupId != 0 {
		gameId := listGroup(groupId)
		if len(gameId) == 0 { //说明没有游戏可以查看
			return gg, 0, nil
		}
		gameIdStr := Join(gameId, ",")
		where = where + fmt.Sprintf(" AND game_id IN (%v) ", gameIdStr)
	}
	sql := fmt.Sprintf(`
		SELECT
			game_id,name
		FROM
			%v
		WHERE
			%v
		LIMIT ?,?
	`, TableGameList, where)
	o := NewDBOrm(DBChessSysData)
	_, err := o.Raw(sql, limit, p.PageSize).QueryRows(&gg)
	if CheckNoExist(err) {
		return gg, 0, nil
	}
	if CheckError(err) {
		return gg, 0, err
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
		`, TableGameList, where)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return gg, 0, err
	}
	return gg, count, nil
}

//获取今天的实时的用户统计
func getTodayUserStat() orm.Params {
	var (
		t              = time.Now()
		lastMonth      = GetSpecifyDayByTimestamp(t.Unix(), -30)
		start, end     = StartEndTimeByTimestamp(t.Unix())                   //
		spStart, spEnd = StartEndTimeByTimestamp(t.AddDate(0, 0, -1).Unix()) //次日留存率的时间
		dau            int64
		mau            int64 //月活
		userNum        int64 //每天在玩的玩家数
		dayRegister    int64
		totalRegister  int64
		retentionRate  float64 //留存率
	)

	//日活
	sqlDau := fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			logintime >=%v AND logintime <= %v
	`, TableClientUser, start, end)
	//月活
	sqlMau := fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			logintime >=%v AND logintime <= %v
	`, TableClientUser, lastMonth, t.Unix())
	//day_register,日注册
	sqlDayRegister := fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			rtime >=%v AND rtime <= %v
	`, TableClientUser, start, end)
	//total_register,累计注册
	sqlTotalRegister := fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
	`, TableClientUser)
	//留存率
	sqlRetentionRate := fmt.Sprintf(`
		SELECT
			((SELECT COUNT(1) FROM %v WHERE logintime >= %v AND logintime <= %v
			AND rtime >= ? AND rtime <= ?) /
			(SELECT COUNT(1) FROM %v WHERE rtime >= ? AND rtime <= ?))
	`, TableClientUser, start, end, TableClientUser)
	//每天游戏人数
	sqlUser := fmt.Sprintf(`
		SELECT
			COUNT(DISTINCT r.uid) AS user_num
		FROM
			%v AS p
		LEFT JOIN
			%v AS r
		ON p.tableid = r.tableid
		WHERE
			p.endtime >= %v AND p.endtime <= %v
	`, TableRoom, TableRoomLog, start, end)

	o := NewDBOrm(DBChess)
	err := o.Raw(sqlDau).QueryRow(&dau)
	if err != nil {
		LogPc.Error(err.Error())
	}
	err = o.Raw(sqlMau).QueryRow(&mau)
	if err != nil {
		LogPc.Error(err.Error())
	}

	err = o.Raw(sqlUser).QueryRow(&userNum)
	if err != nil {
		LogPc.Error(err.Error())
	}

	err = o.Raw(sqlDayRegister).QueryRow(&dayRegister)
	if err != nil {
		LogPc.Error(err.Error())
	}
	err = o.Raw(sqlTotalRegister).QueryRow(&totalRegister)
	if err != nil {
		LogPc.Error(err.Error())
	}
	err = o.Raw(sqlRetentionRate, spStart, spEnd, spStart, spEnd).QueryRow(&retentionRate)
	if err != nil {
		LogPc.Error(err.Error())
	}

	data := map[string]interface{}{
		"create_time":    t.Unix(),
		"dau":            dau,
		"mau":            mau,
		"user_num":       userNum,
		"day_register":   dayRegister,
		"total_register": totalRegister,
		"two_day":        retentionRate * 10000,
	}
	spStart, spEnd = StartEndTimeByTimestamp(t.AddDate(0, 0, -2).Unix()) //3日留存率的时间
	err = o.Raw(sqlRetentionRate, spStart, spEnd, spStart, spEnd).QueryRow(&retentionRate)
	if err != nil {
		LogPc.Error(err.Error())
	}
	data["three_day"] = retentionRate * 10000

	spStart, spEnd = StartEndTimeByTimestamp(t.AddDate(0, 0, -6).Unix()) //7日留存率的时间
	err = o.Raw(sqlRetentionRate, spStart, spEnd, spStart, spEnd).QueryRow(&retentionRate)
	if err != nil {
		LogPc.Error(err.Error())
	}
	data["seven_day"] = retentionRate * 10000
	return data
}

//列出玩家统计数据(day_player_info表)
func ListDayUserStat(p ListParams, op OrderParams, day int64) ([]orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	where := " 1=1 "
	if day != 0 { //如果没有指定查询哪一天就查询最近几天的
		_, end := StartEndTimeByTimestamp(day)
		where = fmt.Sprintf("create_time = %v", end-1)

	}
	orderName := "create_time"
	order := GetOrderSort("DESC", op.Order)
	if op.OrderName != "" {
		orderName = op.OrderName
	}

	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		ORDER BY
			%v %v
		LIMIT ?,?
	`, TableDayPlayerInfo, where, orderName, order)
	o := NewDBOrm(DBChessLog)
	var userInfo []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&userInfo)
	if CheckError(err) {
		return userInfo, 0, err
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
		`, TableDayPlayerInfo, where)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return userInfo, 0, err
	}
	today := getTodayUserStat()
	var tmp []orm.Params
	tmp = append(tmp, today)
	tmp = append(tmp, userInfo...)
	return tmp, count, nil
}

//列出玩家统计数据(day_player_info表),拆线图,用户统计(日活,日注册拆线图)
func ListUserGraph(p ListDayParams) ([]orm.Params, error) {
	sql := fmt.Sprintf(`
		SELECT
			create_time,dau,day_register,user_num
		FROM
			%v
		WHERE
			create_time >= %v AND create_time <= %v
	`, TableDayPlayerInfo, p.StartTime, p.EndTime)
	o := NewDBOrm(DBChessLog)
	var userInfo []orm.Params
	_, err := o.Raw(sql).Values(&userInfo)
	if CheckError(err) {
		return userInfo, err
	}
	return userInfo, nil
}

//列出亲友圈统计数据(day_club,历史数据)
func ListDayClubStat(p ListParams, t ListDayParams, clubId, gameId, hostId, status, password int64) ([]orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	tableName := TableDayClub
	where := " 1=1 "
	if t.StartTime > 0 && t.EndTime > t.StartTime {
		where = fmt.Sprintf("create_time >= %v AND create_time <= %v", t.StartTime, t.EndTime)
	} else if t.StartTime == 0 || t.EndTime == 0 { //查询昨天的
		_, end := StartEndTimeByTimestamp(time.Now().Unix() - 86400) //昨天的时间戳
		where = fmt.Sprintf("create_time = %v", end-1)
	}
	if clubId != 0 {
		where = where + fmt.Sprintf(" AND club_id = %v ", clubId)
	}
	if gameId != 0 {
		where = where + fmt.Sprintf(" AND game_id = %v ", gameId)
	}
	if hostId != 0 {
		where = where + fmt.Sprintf(" AND host_id = %v ", hostId)
	}
	if status != 0 {
		where = where + fmt.Sprintf(" AND status = %v ", status)
	}
	if password != 0 {
		where = where + fmt.Sprintf(" AND password = %v ", password)
	}
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		LIMIT ?,?
	`, tableName, where)
	o := NewDBOrm(DBChessLog)
	var club []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&club)
	if CheckError(err) {
		return nil, 0, err
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
		`, tableName, where)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return nil, 0, err
	}
	return club, count, nil
}

//获取每5分钟在线玩家(及在玩玩家)列表
func ListUserOnlinePlay(p ListDayParams, multiOrm ...orm.Ormer) ([]orm.Params, error) {
	var where string
	var r float64
	sub := p.EndTime - p.StartTime
	if sub < 86400 {
		r = 1
	} else {
		r = math.Ceil(float64(sub / 86400))
	}
	where = fmt.Sprintf(` ctime > %d AND ctime < %d AND id %v %v = 0 `, p.StartTime, p.EndTime, "%", r)
	sql := fmt.Sprintf(`
		SELECT
			ctime,play,online
		FROM
			%v
		WHERE
			%v
	`, TableOnlineCount, where)
	o := NewOrm(multiOrm)
	var userOnlinePlay []orm.Params
	_, err := o.Raw(sql).Values(&userOnlinePlay)
	if CheckNoExist(err) {
		return nil, nil
	}
	return userOnlinePlay, err
}

//获取每30分钟注册的玩家列表
func ListRegisterCount(t ListDayParams, multiOrm ...orm.Ormer) ([]orm.Params, error) {
	where := fmt.Sprintf(` create_time >= %v AND create_time < %v `, t.StartTime, t.EndTime)
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
	`, TableRegisterCount, where)
	o := NewOrm(multiOrm, DBChessLog)
	var userOnlinePlay []orm.Params
	_, err := o.Raw(sql).Values(&userOnlinePlay)
	if CheckNoExist(err) {
		return userOnlinePlay, nil
	}
	return userOnlinePlay, err
}

//列出金流原始的数据
func ListDayTransDetail(p ListParams, day, uid int64) (orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	table := GetTableByTimestamp(TableDayTrans, day)
	if !CheckTableIsExist(table) {
		return nil, 0, nil
	}

	o := NewDBOrm(DBChessLog)
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			uid = %v
		ORDER BY
			tid DESC
		LIMIT ?,?
	`, table, uid)
	var trans []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&trans)
	if CheckError(err) {
		return nil, 0, err
	}
	//统计玩家今天消耗了多少钻石
	var cost int64
	sqlCost := fmt.Sprintf(`
		SELECT
			IF(SUM(amount) is NULL,0,SUM(amount))
		FROM
			%v
		WHERE
			uid = %v AND ptype = %v
	`, table, uid, ACCTRAN_OPER_TYPE_PROOM)
	err = o.Raw(sqlCost).QueryRow(&cost)
	if err != nil {
		return nil, 0, err
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			uid = %v
		`, table, uid)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return nil, 0, err
	}
	return map[string]interface{}{
		"detail": trans,
		"cost":   cost,
	}, count, nil
}

//在线游戏玩家列表(此时此刻在线的,实时在线的)
//已生成配置
func ListOnlinePlayer(p ListParams, uid, clubPassword int64,
	username, clubName, tablePassword string) ([]orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	where := "1=1"
	if uid != 0 {
		where = fmt.Sprintf("o.uid = %v", uid)
	}
	if clubPassword != 0 {
		where = where + fmt.Sprintf(" AND c.password = %v", clubPassword)
	}
	if username != "" {
		where = where + fmt.Sprintf(" AND u.nickname LIKE '%v'", "%"+username+"%")
	}
	if clubName != "" {
		where = where + fmt.Sprintf(" AND c.name LIKE '%v'", "%"+clubName+"%")
	}
	if tablePassword != "" {
		where = where + fmt.Sprintf(" AND r.passwd LIKE '%v'", "%"+tablePassword+"%")
	}

	sql := fmt.Sprintf(`
		SELECT
			o.uid,o.tableid,o.clubid,o.roomid,o.svrid,
			u.*,
			c.name AS club_name,c.password AS club_password,
			n.totalnum,n.maxdelay,n.meandelay,n.lastdelay,n.lastnetname,
			r.passwd AS table_password
		FROM
			%v AS o
		LEFT JOIN
			%v AS u
		ON o.uid = u.uid
		LEFT JOIN
			%v AS c
		ON o.clubid = c.id
		LEFT JOIN
			%v AS n
		ON o.uid = n.uid
		LEFT JOIN
			%v AS r
		ON r.tableid = o.tableid
		WHERE
			%v
		LIMIT ?,?
		`, TableOnlineDetail, TableClientUser, TableClub, TableNetDelay, TableRoom, where)
	o := NewDBOrm(DBChess)
	var user []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&user)
	if CheckError(err) {
		return user, 0, err
	}
	if uid != 0 {
		return user, 1, nil
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(o.uid)
		FROM
			%v AS o
		LEFT JOIN
			%v AS u
		ON o.uid = u.uid
		LEFT JOIN
			%v AS c
		ON o.clubid = c.id
		LEFT JOIN
			%v AS n
		ON o.uid = n.uid
		LEFT JOIN
			%v AS r
		ON r.tableid = o.tableid
		WHERE
			%v
		`, TableOnlineDetail, TableClientUser, TableClub, TableNetDelay, TableRoom, where)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return user, 0, err
	}
	return user, count, nil
}

func getOneDayAgentInfo(p ListParams, op OrderParams, groupId, day, level, uid int64) ([]orm.Params, int64, error) {
	//0-大区,1-一级代理,2-二级代理,3-全部代理"
	limit := (p.PageIndex - 1) * p.PageSize
	var tableName string
	createTime := GetDayByTimestamp(day)
	where := fmt.Sprintf("create_time = %v ", createTime)
	if groupId != 0 {
		where = where + fmt.Sprintf(" AND group_id = %v ", groupId)
	}
	if level == 0 { //大区
		tableName = TableDayGroup
		if uid != 0 {
			where = where + fmt.Sprintf(" AND group_id = %v", uid)
		}
	} else if level == 1 {
		tableName = TableDayAgentLevel1
		if uid != 0 {
			where = where + fmt.Sprintf(" AND agent_id = %v", uid)
		}
	} else {
		tableName = TableDayAgent
		if level == 2 {
			where = where + fmt.Sprintf(" AND level = %v", level)
		}
		if uid != 0 {
			where = where + fmt.Sprintf(" AND agent_id = %v", uid)
		}
	}

	orderName := "create_time"
	order := GetOrderSort("DESC", op.Order)
	if op.OrderName != "" {
		orderName = op.OrderName
	}

	o := NewDBOrm(DBChessLog)
	var data []orm.Params

	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		ORDER BY
			%v %v
		LIMIT ?,?
	`, tableName, where, orderName, order)
	_, err := o.Raw(sql, limit, p.PageSize).Values(&data)
	if err != nil {
		return data, 0, err
	}
	var count int64
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
	`, tableName, where)
	err = o.Raw(sql).QueryRow(&count)
	if err != nil {
		return data, count, err
	}
	return data, count, nil
}

//列出指定天的代理钻石情况
func ListDayAgentInfo(p ListParams, op OrderParams, groupId, day, typ, uid int64) ([]orm.Params, int64, error) {
	return getOneDayAgentInfo(p, op, groupId, day, typ, uid)
}

//列出指定月的代理钻石情况
func ListMonthAgentInfo(p ListParams, op OrderParams, groupId, month, typ, uid int64) ([]orm.Params, int64, error) {
	return getOneMonthAgentInfo(p, op, groupId, month, typ, uid)
}

//获取哪个月的数据
func getOneMonthAgentInfo(p ListParams, op OrderParams, groupId, month, level, uid int64) ([]orm.Params, int64, error) {
	//0-大区,1-一级代理,2-二级代理,3-全部代理"
	limit := (p.PageIndex - 1) * p.PageSize
	var tableName string
	createTime := GetMonthByTimestamp(month)
	where := fmt.Sprintf("create_time = %v ", createTime)
	if groupId != 0 {
		where = where + fmt.Sprintf(" AND group_id = %v ", groupId)
	}
	if level == 0 { //大区
		tableName = TableMonthGroup
		if uid != 0 {
			where = where + fmt.Sprintf(" AND group_id = %v", uid)
		}
	} else if level == 1 {
		tableName = TableMonthAgentLevel1
		if uid != 0 {
			where = where + fmt.Sprintf(" AND agent_id = %v", uid)
		}
	} else {
		tableName = TableMonthAgent
		if level == 2 {
			where = where + fmt.Sprintf(" AND level = %v", level)
		}
		if uid != 0 {
			where = where + fmt.Sprintf(" AND agent_id = %v", uid)
		}
	}
	orderName := "create_time"
	order := GetOrderSort("DESC", op.Order)
	if op.OrderName != "" {
		orderName = op.OrderName
	}

	o := NewDBOrm(DBChessLog)
	var data []orm.Params
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		ORDER BY
			%v %v
		LIMIT ?,?
	`, tableName, where, orderName, order)
	_, err := o.Raw(sql, limit, p.PageSize).Values(&data)
	if err != nil {
		return data, 0, err
	}
	var count int64
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
	`, tableName, where)
	err = o.Raw(sql).QueryRow(&count)
	if err != nil {
		return data, count, err
	}
	return data, count, nil
}

//-----------------------------------------系统帐号流水-------------------------------------
//列出系统帐号流水日报表统计数据(day_system_user,历史数据)
func ListDaySystemUser(p ListParams, t ListDayParams, operateType, sysUid int64) ([]orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	condition := " 1=1 "
	if t.StartTime > 0 && t.EndTime > t.StartTime {
		condition = condition + fmt.Sprintf(" AND create_time >= %v AND create_time <= %v", t.StartTime, t.EndTime)
	}
	if operateType != 0 {
		condition = condition + fmt.Sprintf(" AND operate_type = %v ", operateType)
	}

	if sysUid != 0 {
		condition = condition + fmt.Sprintf(" AND sys_uid = %v ", sysUid)
	}

	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		ORDER BY
			create_time DESC
		LIMIT ?,?
	`, TableDaySystemUser, condition)
	o := NewDBOrm(DBChessLog)
	var club []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&club)
	if CheckError(err) {
		return nil, 0, err
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
		`, TableDaySystemUser, condition)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return club, 0, err
	}
	return club, count, nil
}

//列出系统帐号流水月报表统计数据(month_system_user,历史数据)
func ListMonthSystemUser(p ListParams, t ListDayParams, operateType, sysUid int64) ([]orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	condition := " 1=1 "
	if t.StartTime > 0 && t.EndTime > t.StartTime {
		startMonth := ThisMonthByTimestamp(t.StartTime) //当前时间戳的所在月201803
		endMonth := ThisMonthByTimestamp(t.EndTime)     //当前时间戳的所在月201803
		condition = condition + fmt.Sprintf(" AND create_time >= %v AND create_time <= %v", startMonth, endMonth)
	}
	if operateType != 0 {
		condition = condition + fmt.Sprintf(" AND operate_type = %v ", operateType)
	}

	if sysUid != 0 {
		condition = condition + fmt.Sprintf(" AND sys_uid = %v ", sysUid)
	}

	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		ORDER BY
			create_time DESC
		LIMIT ?,?
	`, TableMonthSystemUser, condition)
	o := NewDBOrm(DBChessLog)
	var club []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&club)
	if CheckError(err) {
		return club, 0, err
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
		`, TableMonthSystemUser, condition)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return club, 0, err
	}
	return club, count, nil
}

//列出系统帐号流水详细数据(system_user_tran)
func ListSystemUserDetail(p ListParams, t ListDayParams, operateType, sysUid, operateUid int64) ([]orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	condition := " 1=1 "
	if t.StartTime > 0 && t.EndTime > t.StartTime {
		condition = condition + fmt.Sprintf(" AND create_time >= %v AND create_time <= %v", t.StartTime, t.EndTime)
	}
	if operateType != 0 {
		condition = condition + fmt.Sprintf(" AND operate_type = %v ", operateType)
	}

	if sysUid != 0 {
		condition = condition + fmt.Sprintf(" AND sys_uid = %v ", sysUid)
	}

	if operateUid != 0 {
		condition = condition + fmt.Sprintf(" AND operate_uid = %v ", operateUid)
	}
	//table := GetMonthTableByTimestamp(TableSystemUserTran, t.StartTime)
	table := TableSystemUserTran
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			%v
		ORDER BY
			create_time DESC
		LIMIT ?,?
	`, table, condition)
	o := NewDBOrm(DBChessLog)
	var club []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&club)
	if CheckError(err) {
		return nil, 0, err
	}
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
		`, table, condition)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	if CheckError(err) {
		return club, 0, err
	}
	return club, count, nil
}

//列出系统帐号钻石消耗情况(system_user)
func ListSystemUser() ([]orm.Params, error) {
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
	`, TableSystemUser)
	o := NewDBOrm(DBChessCenter)
	var club []orm.Params
	_, err := o.Raw(sql).Values(&club)
	if CheckError(err) {
		return club, err
	}
	return club, nil
}
